package dao;
import java.sql.*;
import java.util.ArrayList;

import vo.Score;

public class ScoreDao {
	private Connection conn=null;
	
	public void initConnection()throws Exception{	
		
		String driverClass="com.mysql.jdbc.Driver";
	    String url="jdbc:mysql://localhost:3306/vote?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
	    String user="root";
	    String password="womenilu2580";
	    Class.forName(driverClass);
	    conn=DriverManager.getConnection(url,user,password);	   
	}
	
	//返回某门课程所有学生的分数
	public ArrayList getAllScoresByCourseno(String courseno)throws Exception{
		ArrayList al=new ArrayList();
		initConnection();
		String sql="SELECT name.SNO,name.SNAME,SCO.cname,SCO.SCORE FROM name NAME ,SCORE SCO WHERE NAME.SNO=SCO.SNO AND SCO.TNO=?";//验证这个？能不能取到
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, courseno);
		ResultSet rs=ps.executeQuery();
		while(rs.next())
		{
			Score score=new Score();
			score.setStuno(rs.getString("SNO"));
			score.setStuname(rs.getString("SNAME"));
			score.setType(rs.getString("CNAME"));
			score.setScorenumber(rs.getString("SCORE"));
			al.add(score);
		}		
		conn.close();
		return al;		
	}
	
	//修改某些学生的分数
	public void updateScore(String courseno,String[] type,String[] stuno,String[] score)throws Exception{
		initConnection();
		String sql="UPDATE SCORE SET SCORE=? WHERE SNO=? AND CNAME=? AND TNO=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		for(int i=0;i<stuno.length;i++)
		{
			if(!score[i].equals(""))
			{
				ps.setDouble(1, Double.parseDouble(score[i]));
				ps.setString(2, stuno[i]);
				ps.setString(3, type[i]);
				ps.setString(4, courseno);
				ps.executeUpdate();
				System.out.println("scoreDaoUpdate:"+stuno[i]+"号学生的成绩为"+Double.parseDouble(score[i]));
			}
		}
		ps.close();
		conn.close();	
	}
	
	//删除某个学生的成绩
	public void deleteScore(String stuno)throws Exception{
		initConnection();
		String sql="UPDATE SCORE SET SCORE=NULL WHERE SNO="+stuno+"";
		Statement stat=conn.createStatement();
		int i=stat.executeUpdate(sql);
		System.out.println("成功添加"+i+"行");
    	System.out.println("scoreDaoDelete:"+stuno+"号学生成绩被删除");
		stat.close();
		conn.close();
	}
	

}
